<?
//include all support file
include_once ("../modconfig.php");
include_once ("$ROOT_CLASS_DIR/class_database.php");
include_once ("$ROOT_CLASS_DIR/class_common.php");
include_once ("$ROOT_CLASS_DIR/class_login.php");

require_once('OLEwriter.php');
require_once('BIFFwriter.php');
require_once('Worksheet.php');
require_once('Workbook.php');

global $DBCoins,$DBGeneral,$DBConnection, $RS,$Index, $a, $b,$_CYear,$_CMonth,$_SMonth, $_LastMonth, $LMonth, $_LSMonth;


function HeaderingExcel($filename){
    header("Content-type:application/vnd.ms-excel");
    header("Content-Disposition:attachment;filename=$filename");
    header("Expires:0");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Pragma: public");
}

function GetFromDate () {
    if (GetParam("m","")=="AUDIT")
    list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,12,1,GetParam("y",""))));
    else
 	list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,GetParam("m",""),1,GetParam("y",""))));
 	return array($Day,$Month,$Year,$NMonth);
}

function GetLastPeriod ($Month, $Year) {
	list($LDay,$LMonth,$LYear,$LNMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,$Month,0,$Year)));
	return array($LMonth,$LYear,$LNMonth);
}



function GetSaldoAwal($Month,$year) {
		    $DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
			if ($Month<10) $Month = "0".$Month; // bulan 9 ke bawah, perlu tambah '0' --> '01', dst
			// untuk bulan 10 
			if (($Month-1)<10) $LastMonth = "0".($Month-1);

			// untuk bulan 11 ke atas
			if ($Month>10) $LastMonth = $Month-1;

			if ($Month<2) $LastMonth = "awal";
			else {$LastMonth = "aku".$LastMonth;}

			$SQL = "SELECT $LastMonth as lastmonth FROM tblcoa WHERE ledger='$year' ";
            if (GetParam("o","")) $SQL .= " ktrasl='".GetParam("ktrasl","")."'";

			//print $SQL;die();
                        
			return $DBConnection->dbc->get_var($SQL);
}

function ReadCOA($no) {
		    $DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
 			$SQL = "SELECT namacc FROM tblcoa WHERE skada='".$no."'";
			$MyResult = $DBConnection->dbc->get_var($SQL);
			return $MyResult;
}

function BuildPeriod ($year, $month) {
  $arraymonth = array("","JANUARI","FEBRUARI","MARET","APRIL","MEI","JUNI","JULI","AGUSTUS","SEPTEMBER","OKTOBER","NOPEMBER","DESEMBER");
  return $arraymonth[$month].", $year";
}

$title='LEDGER';
if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));
else $perush = 'PT. MASAJI TATANAN CONTAINER';

if (GetParam("m","")=="AUDIT")
    list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,12,1,GetParam("y",""))));
    else
list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,GetParam("m",""),1,GetParam("y",""))));
$namafile = "Ledger_".GetParam("ktrasl","")."_".$Month.$Year;


//http headers
HeaderingExcel($namafile);

//make a workbook
$workbook=new Workbook("-");

//make first worksheet
$fJudul=& $workbook->add_format();
$fJudul->set_bold();
$fJudul->set_size(14);

$fJudulKolom=& $workbook->add_format();
$fJudulKolom->set_bold();
$fJudulKolom->set_size(8);
$fJudulKolom->set_align('centre');
$fJudulKolom->set_border(1);

$fJudulKolom1=& $workbook->add_format();
$fJudulKolom1->set_bold();
$fJudulKolom1->set_size(8);
$fJudulKolom1->set_align('left');
$fJudulKolom1->set_border(0);

$fBesar=& $workbook->add_format();
$fBesar->set_size(20);

$fKutipan=& $workbook->add_format();
$fKutipan->set_align('right');
$fKutipan->set_italic();
$fKutipan->set_size(8);
$fKutipan->set_color('blue');

$fList=& $workbook->add_format();
$fList->set_size(8);

$fIsi=& $workbook->add_format();
$fIsi->set_border(1);

$fIsiR=& $workbook->add_format();
$fIsiR->set_border(0);
$fIsiR->set_align('right');

$fIsiTengah=& $workbook->add_format();
$fIsiTengah->set_border(1);
$fIsiTengah->set_align('centre');


$angka=& $workbook->add_format();
$angka->set_num_format('#,###');
$angka->set_border(1);

$angka2=& $workbook->add_format();
$angka2->set_num_format('#,##0.#0');
$angka2->set_border(1);


$angka2B=& $workbook->add_format();
$angka2B->set_num_format('#,##0.#0');
$angka2B->set_border(1);
$angka2B->set_bold();

$angkatengah=& $workbook->add_format();
$angkatengah->set_num_format('#,###');
$angkatengah->set_border(1);
$angkatengah->set_align('centre');

$tanggal=& $workbook->add_format();
$tanggal->set_num_format('D-MMM-YYYY');
$tanggal->set_border(1);

$worksheet1= & $workbook->add_worksheet("Ledger");
//seting zoom size
//75 is 75% zoom size
$worksheet1->set_zoom(100);

//set portrait page
$worksheet1->set_portrait(100);

//set a4 paper size
$worksheet1->set_paper(9);

//set hide gridlines
$worksheet1->hide_gridlines();

//set print area
$worksheet1->print_area(0,0,15,5);

//set page header
$worksheet1->set_header("header",$margin=2);


//set column
$worksheet1->set_column(1,1,10);
$worksheet1->set_column(2,2,15);
$worksheet1->set_column(3,3,6);
$worksheet1->set_column(4,4,6);
$worksheet1->set_column(5,5,12);
$worksheet1->set_column(6,6,15);
$worksheet1->set_column(7,7,8);
$worksheet1->set_column(8,8,40);
$worksheet1->set_column(9,9,40);
$worksheet1->set_column(10,10,6);
$worksheet1->set_column(11,11,16);
$worksheet1->set_column(12,12,10);
$worksheet1->set_column(13,13,16);
$worksheet1->set_column(14,14,16);
$worksheet1->set_column(15,15,9);
$worksheet1->set_column(16,16,9);
$worksheet1->set_column(17,17,9);
$worksheet1->set_column(18,18,15);

// buat koneksi database dan cari data
$DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

			if ($Month<10) $Month = "0".$Month; // bulan 9 ke bawah, perlu tambah '0' --> '01', dst
			// untuk bulan 10 
			if (($Month-1)<10) $LastMonth = "0".($Month-1);

			// untuk bulan 11 ke atas
			if ($Month>10) $LastMonth = $Month-1;

			if ($Month<2) $LastMonth = "awal";
			else {$LastMonth = "aku".$LastMonth;}


if (GetParam("f_c","")) 
   	$WHERE = "  ais_memorial.skada='".GetParam("f_c","")."' ";
if (GetParam("m","") && GetParam("y",""))
	if (strlen($WHERE)) $WHERE .= " AND MONTH(tglbkt)='".GetParam("m","")."' AND YEAR(tglbkt)='".GetParam("y","")."'";
		else $WHERE .= " MONTH(tglbkt)='".GetParam("m","")."' AND YEAR(tglbkt)='".GetParam("y","")."'";
if (GetParam("o",""))
    	if (strlen($WHERE)) $WHERE .= " AND ais_memorial.ktrasl='".GetParam("o","")."'";
    	else $WHERE .= " ais_memorial.ktrasl='".GetParam("o","")."'";

$SQL  = " SELECT ais_memorial.kdact,ais_memorial.debtur,ais_memorial.debnam,ais_memorial.noref,ais_memorial.rate,
	ais_memorial.ktrasl,ais_memorial.nobkt,	ais_memorial.tglbkt,ais_memorial.skada,
	ais_memorial.uraian,ais_memorial.dk,ais_memorial.jumlah,ais_memorial.rupiah,$LastMonth as lastmonth,
	ais_memorial.curr,DATEDIFF(tglbkt,str_to_date( '1899-12-30', '%Y-%m-%d' ) ) AS trdate
 	FROM ais_memorial INNER JOIN tblcoa ON tblcoa.skada=ais_memorial.skada ";

 if (strlen($WHERE)) $SQL .= " WHERE ".$WHERE;
 	$SQL .= " and tblcoa.ledger='".GetParam("y","")."' ORDER BY ais_memorial.ktrasl, ais_memorial.SKADA, ais_memorial.nobkt";
 //print $SQL;die();
 //"WHERE skada='".GetParam("c","")."'";
$MyResult = $DBConnection->dbc->get_results($SQL,ARRAY_A);
$baris = 0;			
if ($MyResult) {
	foreach($MyResult as $row) {

		if ($row["dk"]=='D') $row["rupiah1"] = $row["rupiah"];
	    	else $row["rupiah2"] = $row["rupiah"];
		$totald = $totald + $row["rupiah1"];
		$totalc = $totalc + $row["rupiah2"];
	
	        if (($skada<>$row["skada"]) && (strlen($skada)>0)) {
		//if ($skada<>$row["skada"])  {                                
			$worksheet1->write_string($baris,12,"SUB TOTAL:",$fIsiR);
			//$worksheet1->write_string($baris,10,"",$fIsiR);
			//$worksheet1->write_string($baris,11,"",$fIsiR);
			if ($subtotald>0) {$worksheet1->write_number($baris,13,$subtotald,$angka2B);}
			else {$worksheet1->write_string($baris,13,"",$fIsiR);}
			if ($subtotalc>0) {$worksheet1->write_number($baris,14,$subtotalc,$angka2B);}
			else {$worksheet1->write_string($baris,14,"",$fIsiR);}
			$baris = $baris+1;
			$worksheet1->write_string($baris,12,"TOTAL:",$fIsiR);
			//$worksheet1->write_string($baris,10,"",$fIsiR);
			//$worksheet1->write_string($baris,11,"",$fIsiR);
			if ($subtotald+$SaldoDebet>0) {$worksheet1->write_number($baris,13,$subtotald+$SaldoDebet,$angka2B);}
			else {$worksheet1->write_string($baris,13,"",$fIsiR);}
			if ($subtotalc+$SaldoKredit>0) {$worksheet1->write_number($baris,14,$subtotalc+$SaldoKredit,$angka2B);}
			else {$worksheet1->write_string($baris,14,"",$fIsiR);}
			$baris = $baris+1;
			$worksheet1->write_string($baris,12,"SALDO AKHIR:",$fIsiR);
			//$worksheet1->write_string($baris,10,"",$fIsiR);
			//$worksheet1->write_string($baris,11,"",$fIsiR);
			$Balance = ($subtotald+$SaldoDebet)-($subtotalc+$SaldoKredit);
			if ($Balance>0) {
				$worksheet1->write_number($baris,13,$Balance,$angka2B);
				$worksheet1->write_string($baris,14,"",$fIsi);
			} else {
				$worksheet1->write_string($baris,13,"",$fIsi);
				$worksheet1->write_number($baris,14,$Balance,$angka2B);
			}
			$baris = $baris+1;
  	        	$subtotald = 0;
	        	$subtotalc = 0;
			$SaldoDebet = 0;
			$SaldoKredit = 0;

			$n=0;
        	}

		if ($SKADA<>$row["skada"]) {
			$baris = $baris+1;
			$SKADA=$row["skada"];
			$ACCOUNT = $row["skada"];
			//Header
			if (!strlen($ACCOUNT)) $ACCOUNT = GetParam("f_c","");

				$worksheet1->write_string($baris,1,$perush,$fJudul);
				$worksheet1->write_string($baris+1,1,"LEDGER PERIOD: ".BuildPeriod (GetParam("y",""), GetParam("m","")),$fJudul);
				$worksheet1->write_string($baris+2,1,"REPORT DATE: ".date("d/m/y"),$fJudul);
				$worksheet1->write_string($baris+4,1,"ACCOUNT : ",$fJudulKolom1);
				$worksheet1->write_string($baris+4,2,$ACCOUNT." - ".ReadCOA($ACCOUNT),$fJudulKolom1);
				$worksheet1->write_string($baris+5,1,"No",$fJudulKolom);
				$worksheet1->write_string($baris+5,2,"TRANS. NO",$fJudulKolom);
				$worksheet1->write_string($baris+5,3,"KTR",$fJudulKolom);
				$worksheet1->write_string($baris+5,4,"UU",$fJudulKolom);
				$worksheet1->write_string($baris+5,5,"DATE",$fJudulKolom);
				$worksheet1->write_string($baris+5,6,"REF. NO",$fJudulKolom);
				$worksheet1->write_string($baris+5,7,"DEBTUR",$fJudulKolom);
				$worksheet1->write_string($baris+5,8,"DEBNAM",$fJudulKolom);
				$worksheet1->write_string($baris+5,9,"DESCRIPTION",$fJudulKolom);
				$worksheet1->write_string($baris+5,10,"CURR",$fJudulKolom);
				$worksheet1->write_string($baris+5,11,"AMOUNT",$fJudulKolom);
				$worksheet1->write_string($baris+5,12,"RATE",$fJudulKolom);
				$worksheet1->write_string($baris+5,13,"DEBET",$fJudulKolom);
				$worksheet1->write_string($baris+5,14,"CREDIT",$fJudulKolom);
				$baris = $baris+6;
		//*********** SALDO AWAL *************
		//if (($skada<>$row["skada"])) {
		   	$Saldo = $row["lastmonth"];
			if ($Saldo>0) $SaldoDebet = $Saldo;
			else {$SaldoKredit = $Saldo; $SaldoKredit=$SaldoKredit*-1;}
			$worksheet1->write_string($baris,12,"SALDO AWAL: ",$fIsiR);
			if ($SaldoDebet>0) {$worksheet1->write_number($baris,13,$SaldoDebet,$angka2B);}
			else {$worksheet1->write_string($baris,13,'',$fIsi);}
			if ($SaldoKredir>0) {$worksheet1->write_number($baris,14,$SaldoKredit,$angka2B);}
			else {$worksheet1->write_string($baris,14,'',$fIsi);}
			$baris = $baris+1;
	    	//}
		//*********** SALDO AWAL *************
		}
		
	    
          	$n++;
		$worksheet1->write_number($baris,1,$n,$angka);
		$worksheet1->write_string($baris,2,$row["nobkt"],$fIsi);
		$worksheet1->write_string($baris,3,$row["ktrasl"],$fIsi);
		$worksheet1->write_string($baris,4,$row["kdact"],$fIsi);
		$worksheet1->write_number($baris,5,$row["trdate"],$tanggal);
		$worksheet1->write_string($baris,6,$row["noref"],$fIsi);
		$worksheet1->write_string($baris,7,$row["debtur"],$fIsi);
		$worksheet1->write_string($baris,8,$row["debnam"],$fIsi);
		$worksheet1->write_string($baris,9,$row["uraian"],$fIsi);
		$worksheet1->write_string($baris,10,$row["curr"],$fIsi);
		$worksheet1->write_number($baris,11,$row["jumlah"],$angka2);
		$worksheet1->write_number($baris,12,$row["rate"],$angka2);
		$worksheet1->write_number($baris,13,$row["rupiah1"],$angka2);
		$worksheet1->write_number($baris,14,$row["rupiah2"],$angka2);
	        $baris = $baris+1;
	        

	        $subtotald = $subtotald + $row["rupiah1"];
	        $subtotalc = $subtotalc + $row["rupiah2"];
		$skada = $row["skada"];

	} // foreach

	$worksheet1->write_string($baris,12,"SUB TOTAL:",$fIsiR);
	//$worksheet1->write_string($baris,10,"",$fIsiR);
	//$worksheet1->write_string($baris,11,"",$fIsiR);
	if ($subtotald>0) {$worksheet1->write_number($baris,13,$subtotald,$angka2B);}
	else {$worksheet1->write_string($baris,13,"",$fIsiR);}
	if ($subtotalc>0) {$worksheet1->write_number($baris,14,$subtotalc,$angka2B);}
	else {$worksheet1->write_string($baris,14,"",$fIsiR);}
	$baris = $baris+1;
	$worksheet1->write_string($baris,12,"TOTAL:",$fIsiR);
	//$worksheet1->write_string($baris,10,"",$fIsiR);
	//$worksheet1->write_string($baris,11,"",$fIsiR);
	if ($subtotald+$SaldoDebet>0) {$worksheet1->write_number($baris,13,$subtotald+$SaldoDebet,$angka2B);}
	else {$worksheet1->write_string($baris,13,"",$fIsiR);}
	if ($subtotalc+$SaldoKredit>0) {$worksheet1->write_number($baris,14,$subtotalc+$SaldoKredit,$angka2B);}
	else {$worksheet1->write_string($baris,14,"",$fIsiR);}
	$baris = $baris+1;
	$worksheet1->write_string($baris,12,"SALDO AKHIR:",$fIsiR);
	//$worksheet1->write_string($baris,10,"",$fIsiR);
	//$worksheet1->write_string($baris,11,"",$fIsiR);
	$Balance = ($subtotald+$SaldoDebet)-($subtotalc+$SaldoKredit);
	if ($Balance>0) {
		$worksheet1->write_number($baris,13,$Balance,$angka2B);
		$worksheet1->write_string($baris,14,"",$fIsi);
	} else {
		$worksheet1->write_string($baris,13,"",$fIsi);
		$worksheet1->write_number($baris,14,$Balance,$angka2B);
	}
} // if ($MyResult) 


$workbook->close();
?>
